What is field separator?

--_000_C8273570AF06tsakaigalloucsfedu_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

Hi Everybody,

I am a bit confused about field separator.
In psql, \pset command lets me change this value from the default
to something else. I thought field separator was =93|=94 below:

marker | allele | afreq | fam_ | p
------------+--------+-------+------+----------
RS2312724 | 2 | 0.125 | 25 | 0.271484
RS1020382 | 2 | 0.417 | 69 | 0.609682
RS11878315 | 2 | 0.335 | 64 | 0.354771

Can somebody please tell me what =93|=94 is called in my example above?
And is it possible to change it from =93|=94 to something else for
display purpose?

Thank you.

Tena Sakai
tsakai@gallo.ucsf.edu

--_000_C8273570AF06tsakaigalloucsfedu_
Content-Type: text/html; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable


252">


Hi Everybody,



I am a bit confused about field separator.

In psql, \pset command lets me change this value from the default

to something else.  I thought field separator was =93|=94 below:

, Helvetica, Arial">

  marker   | allele =
| afreq | fam_ |    p

------------+--------+-------+------+----------   =
; 

 RS2312724  | 2      | 0.125 | 25  =
 | 0.271484

 RS1020382  | 2      | 0.417 | 69  =
 | 0.609682

 RS11878315 | 2      | 0.335 | 64   =
;| 0.354771



Can somebody please tell me what =93|=94 is called in my example above?

And is it possible to change it from =93|=94 to something else for

display purpose?



Thank you.



Tena Sakai






--_000_C8273570AF06tsakaigalloucsfedu_--

Re: What is field separator?

Tena Sakai ha scritto:
> Can somebody please tell me what =93|=94 is called in my example above?
> And is it possible to change it from =93|=94 to something else for
> display purpose?
The field separatore in psql is used only when the format is "unaligned"=20
(which does not happen to be default).

In order for it to be effective, you need to type in psql:

\pset format unaligned
\pset fieldsep '\t'

This will give you an output with columns that are separated by tab.

However, I suggest that you look at the documentation for more options=20
and ways to achive the same results:=20
http://www.postgresql.org/docs/current/static/app-psql.html

Cheers,
Gabriele

--=20
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: What is field separator?

Thank you, Gabriele.

Now I realize "context sensitive" nature of field separator.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


On 5/30/10 1:37 AM, "Gabriele Bartolini"
wrote:

> Tena Sakai ha scritto:
>> Can somebody please tell me what =B3|=B2 is called in my example above?
>> And is it possible to change it from =B3|=B2 to something else for
>> display purpose?
> The field separatore in psql is used only when the format is "unaligned"
> (which does not happen to be default).
>=20
> In order for it to be effective, you need to type in psql:
>=20
> \pset format unaligned
> \pset fieldsep '\t'
>=20
> This will give you an output with columns that are separated by tab.
>=20
> However, I suggest that you look at the documentation for more options
> and ways to achive the same results:
> http://www.postgresql.org/docs/current/static/app-psql.html
>=20
> Cheers,
> Gabriele


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: What is field separator?

--_000_C829B239AF3Btsakaigalloucsfedu_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

Hi Gabriele,

This isn=92t strictly a psql problem, but please humor me.

I can do below from linux prompt:
$ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
> psql -P 'format=3Dunaligned' -P 'fieldsep=3D,' -t -f - musket
And get:
RS3094315,0.578121,0.735
RS3094315,0.578121,0.265
RS3115850,0.365978,0.251
RS3115850,0.365978,0.749
RS12562034,,0.135
RS12562034,,0.865
RS4475691,0.247133,0.710
RS4475691,0.247133,0.290
RS28705211,,0.725
RS28705211,,0.275

But when I do this:
$ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
> psql -P 'format=3Dunaligned' -P 'fieldsep=3D\t' -t -f - musket
(here the only change from the previous is the specification of fieldsep)
Then I get:
RS3094315\t0.578121\t0.735
RS3094315\t0.578121\t0.265
RS3115850\t0.365978\t0.251
RS3115850\t0.365978\t0.749
RS12562034\t\t0.135
RS12562034\t\t0.865
RS4475691\t0.247133\t0.710
RS4475691\t0.247133\t0.290
RS28705211\t\t0.725
RS28705211\t\t0.275

What's denoted as \t is not a tab character. It is a two character
sequence backslash followd by a t. I have tried all other possibilities
such as \\t , "\t", etc that I can think of, but I cannot get an honest to
god tab character to appear as field separator. I can do it from psql
and achieve what I want, but I need to do this from shell level. Any
suggestion what else I can try?

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu

On 5/30/10 1:37 AM, "Gabriele Bartolini" > wrote:

> Tena Sakai ha scritto:
>> Can somebody please tell me what =93|=94 is called in my example above?
>> And is it possible to change it from =93|=94 to something else for
>> display purpose?
> The field separatore in psql is used only when the format is "unaligned"
> (which does not happen to be default).
>
> In order for it to be effective, you need to type in psql:
>
> \pset format unaligned
> \pset fieldsep '\t'
>
> This will give you an output with columns that are separated by tab.
>
> However, I suggest that you look at the documentation for more options
> and ways to achive the same results:
> http://www.postgresql.org/docs/current/static/app-psql.html
>
> Cheers,
> Gabriele

--_000_C829B239AF3Btsakaigalloucsfedu_
Content-Type: text/html; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable


252">


11pt">Hi Gabriele,



This isn=92t strictly a psql problem, but please humor me.



I can do below from linux prompt:

  $ echo "select marker, p, afreq from gallo.fbat_sdsdecode =
limit 10" | \

 > psql -P 'format=3Dunaligned' -P =
'fieldsep=3D,' -t -f - musket    &nb=
sp;  

And get:

  RS3094315,0.578121,0.735

  RS3094315,0.578121,0.265

  RS3115850,0.365978,0.251

  RS3115850,0.365978,0.749

  RS12562034,,0.135

  RS12562034,,0.865

  RS4475691,0.247133,0.710

  RS4475691,0.247133,0.290

  RS28705211,,0.725

  RS28705211,,0.275



But when I do this:

  $ echo "select marker, p, afreq from gallo.fbat_sdsdecode =
limit 10" | \

 > psql -P 'format=3Dunaligned' -P =
'fieldsep=3D\t' -t -f - musket

(here the only change from the previous is the specification of fieldsep) r>
Then I get:

  RS3094315\t0.578121\t0.735

  RS3094315\t0.578121\t0.265

  RS3115850\t0.365978\t0.251

  RS3115850\t0.365978\t0.749

  RS12562034\t\t0.135

  RS12562034\t\t0.865

  RS4475691\t0.247133\t0.710

  RS4475691\t0.247133\t0.290

  RS28705211\t\t0.725

  RS28705211\t\t0.275



What's denoted as \t is not a tab character.  It is a two character >
sequence backslash followd by a t.  I have tried all other possibiliti=
es

such as \\t , "\t", etc that I can think of, but I cannot get an =
honest to

god tab character to appear as field separator.  I can do it from psql=


and achieve what I want, but I need to do this from shell level.  Any<=
br>
suggestion what else I can try?



Regards,



Tena Sakai





On 5/30/10 1:37 AM, "Gabriele Bartolini" < bartolini@2ndQuadrant.it">gabriele.bartolini@2ndQuadrant.it> wrote:<=
br>


> Tena Sakai ha scritto:

>> Can somebody please tell me what =
=93|=94 is called in my example above?

>> And is it possible to change it from =93|=94 to something else for=


>> display purpose?

> The field separatore in psql is used on=
ly when the format is "unaligned"

> (which does not happen to be default).

>

> In order for it to be effective, you need to type in psql:

>

> \pset format unaligned

> \pset fieldsep '\t'

>

> This will give you an output with columns that are separated by tab. r>
>

> However, I suggest that you look at the documentation for more options=


> and ways to achive the same results:

> ">http://www.postgresql.org/docs/current/static/app-psql.htm l

>

> Cheers,

> Gabriele






--_000_C829B239AF3Btsakaigalloucsfedu_--

Re: What is field separator?

Tena Sakai wrote:
> $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
> > psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket
> What's denoted as \t is not a tab character. It is a two character
> sequence backslash followd by a t. I have tried all other possibilities
> such as \\t , "\t", etc that I can think of, but I cannot get an honest to
> god tab character to appear as field separator.

Yeah, that's a tough one. In bash you can use ANSI C quoting to pull
this off:

psql -c "select name,setting from pg_settings limit 1" -d postgres -At
-F $'\t'

See http://wiki.bash-hackers.org/syntax/quoting for more information.
Portability to other shells I'm not sure about.

Note that I did two other things a little differently than your example,
as well as tightening up the abbreviations:

-Putting the command in -c " " is just better all around than piping
into psql using echo. It even works with multi-line input, i.e.:

psql -c "
select 1
"

-Explicitly specifying the database manually using -d makes what's
happening easier to follow than expecting people to know the convention
that the first non-option passed to psql is a database name.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: What is field separator?

Excerpts from Tena Sakai's message of lun may 31 21:36:41 -0400 2010:

> But when I do this:
> $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" |=
\
> > psql -P 'format=3Dunaligned' -P 'fieldsep=3D\t' -t -f - musket
> (here the only change from the previous is the specification of fieldse=
p)
> Then I get:
> RS3094315\t0.578121\t0.735

Use a literal tab. You can enter this in decent shells with Ctrl-V follo=
wed by a tab.

--=20
Álvaro Herrera
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: What is field separator?

Many thanks, Greg!

I got it to work by emulating what you wrote.
But I think you meant -A, not -At below.
>> psql -c "select name,setting from pg_settings limit 1" -d postgres -At
>> -F $'\t'

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


On 5/31/10 8:07 PM, "Greg Smith" wrote:

> Tena Sakai wrote:
>> $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
>>> psql -P 'format=3Dunaligned' -P 'fieldsep=3D\t' -t -f - musket
>> What's denoted as \t is not a tab character. It is a two character
>> sequence backslash followd by a t. I have tried all other possibilities
>> such as \\t , "\t", etc that I can think of, but I cannot get an honest =
to
>> god tab character to appear as field separator.
>=20
> Yeah, that's a tough one. In bash you can use ANSI C quoting to pull
> this off:
>=20
> psql -c "select name,setting from pg_settings limit 1" -d postgres -At
> -F $'\t'
>=20
> See http://wiki.bash-hackers.org/syntax/quoting for more information.
> Portability to other shells I'm not sure about.
>=20
> Note that I did two other things a little differently than your example,
> as well as tightening up the abbreviations:
>=20
> -Putting the command in -c " " is just better all around than piping
> into psql using echo. It even works with multi-line input, i.e.:
>=20
> psql -c "
> select 1
> "
>=20
> -Explicitly specifying the database manually using -d makes what's
> happening easier to follow than expecting people to know the convention
> that the first non-option passed to psql is a database name.


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: What is field separator?

Thank you, Alvaro.

Literal tab, that's one thing I never tried.
Yes, it works.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


On 5/31/10 8:58 PM, "Alvaro Herrera" wrote:

> Excerpts from Tena Sakai's message of lun may 31 21:36:41 -0400 2010:
>=20
>> But when I do this:
>> $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
>>> psql -P 'format=3Dunaligned' -P 'fieldsep=3D\t' -t -f - musket
>> (here the only change from the previous is the specification of fieldsep)
>> Then I get:
>> RS3094315\t0.578121\t0.735
>=20
> Use a literal tab. You can enter this in decent shells with Ctrl-V follo=
wed
> by a tab.


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: What is field separator?

Tena Sakai wrote:
> I got it to work by emulating what you wrote.
> But I think you meant -A, not -At below.
>
>>> psql -c "select name,setting from pg_settings limit 1" -d postgres -At
>>> -F $'\t'
>>>

You had -t in your original to turn off the display of column names and
the counts at the end, and -A is the shortcut for what you had as "-P
'format=unaligned'". I throw "-At" into almost every use of psql from a
bash script I do, that's the usual combination that gets the basic
format to be right; then tweak things like the field separator afterwards.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: What is field separator?

Hi Greg,

My apologies. I hadn't realized that -At was a combination
of -A and -t. Rather, I kept thinking that t as argument
to -A.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


On 6/1/10 11:02 PM, "Greg Smith" wrote:

> Tena Sakai wrote:
>> I got it to work by emulating what you wrote.
>> But I think you meant -A, not -At below.
>> =20
>>>> psql -c "select name,setting from pg_settings limit 1" -d postgres -At
>>>> -F $'\t'
>>>> =20
>=20
> You had -t in your original to turn off the display of column names and
> the counts at the end, and -A is the shortcut for what you had as "-P
> 'format=3Dunaligned'". I throw "-At" into almost every use of psql from a
> bash script I do, that's the usual combination that gets the basic
> format to be right; then tweak things like the field separator afterwards.


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin